﻿'Copyright ?1995-2007, Camstar Systems, Inc. All Rights Reserved.
'Description:外委数据访问类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:tianFW
'create Date:2017-6-19
'Rewriter:
'Rewrite Date:
'Copyright ?1995-2007, Camstar Systems, Inc. All Rights Reserved.

Imports System.Text
Imports DatabaseAccess.DBUtility
Imports BusinessRule.DTO
Imports BusinessRule.IBusiness
Imports System.Data.OracleClient
Imports BusinessRule.Common

Public Class uLMSCommissioningBusiness
    Implements uLMSCommissioningIBusiness
    Private m_ContainerShip As uLMSContainerShipBusiness = New uLMSContainerShipBusiness
    Private m_CommonObj As uLMSCommonBusiness = New uLMSCommonBusiness
    ''' <summary>
    ''' 获取可以进行外委的批次信息
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContianerCommissioningInfo(ByVal QueryCondition As Dictionary(Of String, String)) As DTO.uMESProcessResult Implements IBusiness.uLMSCommissioningIBusiness.GetContianerCommissioningInfo
        Dim strSelect As String =
            <string>
             SELECT C.PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE,C.TOGETHERFLAG,
             C.CONTAINERTYPE,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.PLANQTY,
             C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,(c.productname||'/'||c.containerno) ProductBatch,
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strSelect)
        ' sb.AppendLine("REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME,")
        sb.AppendLine("SUM(C.QTY) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,NULL WORKREPORTNUM,'' FaContianerID,NULL COMMISSIONINGNUM,NULL ISCOMMISSIONING,'' ORIGINALFACTORYID,'' ISHOLD")
        sb.AppendLine(" FROM CONTAINERCURRENTINFO C ")
        If QueryCondition("Status") <> "1" Then
            sb.AppendLine(" left join containercommidetail ccd on ccd.containerid=c.containerid and ccd.workflowstepid=c.workflowstepid and ccd.isuse=0")
            sb.AppendLine(" left join CONTAINERCOMMISSIONING ccg on ccg.containercommissioningid=ccd.containercommissioningid and ccg.isuse=0")
        End If
        Dim sbCondition As String = GetCondition(QueryCondition)
        sbCondition += " AND C.SPLITNUM = 0 " 'AND C.DISPATCHNUM=0 AND C.ISSHIP = 0 AND C.WORKCENTERDISPATCHNUM=0  AND C.WORKCENTERID='{0}' , QueryCondition("WorkCenterID")
        If QueryCondition("Status") = "1" Then
            sbCondition += " AND C.COMMISSIONINGNUM=0"
        ElseIf QueryCondition("Status") = "2" Then
            sbCondition += " AND C.COMMISSIONINGNUM=1"
        ElseIf QueryCondition("Status") = "3" Then
            sbCondition += " AND C.ISCOMMISSIONING=1 "
        Else
            sbCondition += " AND CCG.INEMPLOYEEID IS NOT NULL "
        End If
        If QueryCondition.Keys.Contains("mfgOrderName") Then
            sbCondition += String.Format(" AND C.MFGORDERNAME LIKE '%{0}%'", QueryCondition("mfgOrderName"))
        End If
        sbCondition += " AND C.CONTAINERSTATUS=1 AND C.ISINSTORE = 0" 'add 2017/11/02
        sb.AppendLine(sbCondition)
        sb.AppendLine("AND C.CONTAINERTYPE='7'")
        sb.AppendLine("GROUP BY C.PRODUCTID, C.PRODUCTNAME, C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID, C.WORKFLOWID, C.WORKFLOWSTEPSEQUENCE, C.SPECNO,")
        sb.AppendLine("C.TOGETHERFLAG, C.CONTAINERTYPE,C.MFGORDERNAME,C.PLANQTY, C.PLANNEDSTARTDATE, C.PLANNEDCOMPLETIONDATE,")
        sb.AppendLine("C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,c.containerno")
        sb.AppendLine(" UNION ")
        sb.AppendLine(strSelect)
        ' sb.AppendLine("SUBSTR(C.CONTAINERNAME, LENGTH(C.PRODUCTNAME) + 2) TOGETHERFLAGNAME,")
        sb.AppendLine("C.QTY AS WIPQTY , C.CONTAINERID,C.CONTAINERNAME,C.WORKREPORTNUM,C.CONTAINERID AS FaContianerID,C.COMMISSIONINGNUM,C.ISCOMMISSIONING,C.ORIGINALFACTORYID,C.ISHOLD")
        sb.AppendLine(" FROM CONTAINERCURRENTINFO C ")
        If QueryCondition("Status") <> "1" Then
            sb.AppendLine(" left join containercommidetail ccd on ccd.containerid=c.containerid and ccd.workflowstepid=c.workflowstepid and ccd.isuse=0")
            sb.AppendLine(" left join CONTAINERCOMMISSIONING ccg on ccg.containercommissioningid=ccd.containercommissioningid and ccg.isuse=0")
        End If
        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE<>'7' ")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        If QueryCondition("Status") <> "1" Then
            sbCondition = String.Format("SELECT distinct C.* FROM CONTAINERCURRENTINFO C left join containercommidetail ccd on ccd.containerid=c.containerid and ccd.workflowstepid=c.workflowstepid " +
                                        " left join CONTAINERCOMMISSIONING ccg on ccg.containercommissioningid=ccd.containercommissioningid and ccg.isuse=0 and ccd.isuse=0  {0}", sbCondition)
        Else
            sbCondition = String.Format("SELECT C.* FROM CONTAINERCURRENTINFO C {0}", sbCondition)
        End If
        Return DatabaseHelper.GetContainerForPage(paraPage, sbCondition)
    End Function

    ''' <summary>
    ''' 创建批次查询数据条件
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetCondition(ByVal para As Dictionary(Of String, String)) As String
        Dim sbCondition As New StringBuilder
        sbCondition.AppendFormat(" WHERE C.FACTORYID='{0}' ", para("FactoryID"))
        If para.Keys.Contains("TeamID") Then
            sbCondition.AppendFormat(" AND C.TEAMID='{0}' ", para("TeamID"))
        End If
        If para.Keys.Contains("ProductName") Then
            sbCondition.AppendFormat(" AND C.PRODUCTNAME LIKE'%{0}%'", para("ProductName"))
        End If
        If para.Keys.Contains("StepNo") Then
            sbCondition.AppendFormat(" AND C.SPECNO LIKE'%{0}%'", para("StepNo"))
        End If
        If para.Keys.Contains("ScanContainerName") Then
            sbCondition.AppendFormat(" AND C.CONTAINERNAME = '{0}'", para("ScanContainerName"))
        Else
            If para.Keys.Contains("ContainerName") Then
                sbCondition.AppendFormat(" AND C.CONTAINERNAME LIKE '{0}%'", para("ContainerName"))
            End If
        End If
        GetCondition = sbCondition.ToString
    End Function

    ''' <summary>
    ''' 添加批次外委信息
    ''' </summary>
    ''' <remarks></remarks>
    Public Function AddContainerCommissioning(ByVal dto As ContainerCommissioningDTO) As Boolean Implements IBusiness.uLMSCommissioningIBusiness.AddContainerCommissioning
        Dim strsql As String =
            <string>begin
update ContainerCommiDetail set isuse=1 where CONTAINERCOMMISSIONINGID in (
select CONTAINERCOMMISSIONINGID from CONTAINERCOMMISSIONING where Containerid=:CONTAINERID and WORKFLOWSTEPID=:WORKFLOWSTEPID and INWORKFLOWSTEPID=:INWORKFLOWSTEPID
);
update  CONTAINERCOMMISSIONING set isuse=1 
where Containerid=:CONTAINERID and WORKFLOWSTEPID=:WORKFLOWSTEPID and INWORKFLOWSTEPID=:INWORKFLOWSTEPID;
                
INSERT INTO CONTAINERCOMMISSIONING(CONTAINERID,CONTAINERNAME,FACTORYID,OUTCUSTOMERID,OUTQTY,OUTEMPLOYEEID,WORKFLOWSTEPID,INWORKFLOWSTEPID,CONTAINERCOMMISSIONINGID,ISUSE,ISTRANSFER,proposedate)
VALUES(:CONTAINERID,:CONTAINERNAME,:FACTORYID,:OUTCUSTOMERID,:OUTQTY,:OUTEMPLOYEEID,:WORKFLOWSTEPID,:INWORKFLOWSTEPID,:CONTAINERCOMMISSIONINGID,0,0,sysdate);
       
end;</string>
        Dim insertpara(8) As OracleParameter

        insertpara(0) = DatabaseHelper.NewParameter(":CONTAINERID", dto.ContainerID, OracleType.VarChar)
        insertpara(1) = DatabaseHelper.NewParameter(":CONTAINERNAME", dto.ContainerName, OracleType.VarChar)
        insertpara(2) = DatabaseHelper.NewParameter(":FACTORYID", dto.FactoryID, OracleType.VarChar)
        insertpara(3) = DatabaseHelper.NewParameter(":OUTCUSTOMERID", dto.OutCustomerID, OracleType.VarChar)
        insertpara(4) = DatabaseHelper.NewParameter(":OUTQTY", dto.OutQty, OracleType.Number)
        insertpara(5) = DatabaseHelper.NewParameter(":OUTEMPLOYEEID", dto.OutEmployeeID, OracleType.VarChar)
        insertpara(6) = DatabaseHelper.NewParameter(":WORKFLOWSTEPID", dto.WorkflowStepID, OracleType.VarChar)
        insertpara(7) = DatabaseHelper.NewParameter(":INWORKFLOWSTEPID", dto.InWorkflowStepID, OracleType.VarChar)
        insertpara(8) = DatabaseHelper.NewParameter(":CONTAINERCOMMISSIONINGID", dto.ContainerCommissioningID, OracleType.VarChar)

        If DatabaseHelper.ExecuteSql(strsql, insertpara) > 0 Then
            strsql = <string>select ws.workflowid,ws.workflowstepid,ws.workflowstepname from workflowstep ws
left join workflowstep ws1 on ws1.workflowstepid='{0}'
left join workflowstep ws2 on ws2.workflowstepid='{1}'
where ws.workflowid=ws1.workflowid</string>.Value
            strsql += " and ws.sequence>=ws1.sequence and ws.sequence<ws2.sequence "
            Dim stepDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(strsql, dto.WorkflowStepID, dto.InWorkflowStepID))
            Dim insertSql = <string>insert into ContainerCommiDetail (ContainerID,ContainerCommissioningID,ContainerCommiDetailID,IsUse,WorkflowID,WorkflowStepID,
WorkflowStepName) values ('{0}','{1}','{2}',0,'{3}','{4}','{5}')</string>.Value
            Dim insertSqlList As New ArrayList
            For Each stepRow As DataRow In stepDt.Rows
                insertSqlList.Add(String.Format(insertSql, dto.ContainerID, dto.ContainerCommissioningID, Guid.NewGuid().ToString, stepRow("WorkflowID").ToString,
                                  stepRow("workflowstepid").ToString, stepRow("workflowstepname").ToString))
            Next
            DatabaseHelper.ExecuteSqlTran(insertSqlList)

            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 按唯一ID删除外委信息
    ''' </summary>
    ''' <param name="ContianerCommissioningID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DelContainerCommissioningInfo(ByVal ContianerCommissioningID As String) As Boolean Implements IBusiness.uLMSCommissioningIBusiness.DelContainerCommissioningInfo
        Dim strsql As String = String.Format("UPDATE CONTAINERCOMMISSIONING C SET C.ISUSE =1,C.ISTRANSFER=0 WHERE C.CONTAINERCOMMISSIONINGID='{0}'", ContianerCommissioningID)
        If DatabaseHelper.ExecuteSql(strsql) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 撤销外委数据
    ''' add:Wangjh 20220705
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ReverseCommissionInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSCommissioningIBusiness.ReverseCommissionInfo
        Dim re = New uMESProcessResult
        re.Result = False

        Dim strSql = <string>select distinct cci.workflowstepid cuworkflowstepid,cci.containertype,cm.* from containercommissioning cm
left join containercurrentinfo cci on cci.containerid=cm.containerid
where 1=1 </string>.Value
        If para.ContainsKey("ContainerCommissioningIDs") Then
            strSql += String.Format(" and cm.containercommissioningid in ({0}) ", para("ContainerCommissioningIDs"))
        End If

        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dt.Rows.Count = 0 Then
            re.Message = "未找到数据"
            Return re
        End If

        Dim strSqlList As New ArrayList
        Dim changeQtySql = <string>INSERT INTO CheckQtyChangeRecord(Checkqtychangerecordid,Containercheckinfoid,Containerid,Changeqtytype,Isuse,Qty,Reasonid,Scrapsourcetype,
Containerstepworkreportid,Createdate,Employeeid,Workflowstepid) VALUES(sys_guid(),'','{0}',1,0,{1},'外委撤销',8,'',Sysdate,'{2}','{3}')</string>.Value

        Dim updateChangeQtySql = "update CheckQtyChangeRecord set isuse=1 where ContainerCheckInfoID='{0}' and ScrapSourceType=5"

        For Each dr As DataRow In dt.Rows
            strSqlList.Clear()
            strSqlList.Add(String.Format("update ContainerCommiDetail set ISUSE=1 where CONTAINERCOMMISSIONINGID='{0}' ", dr("containercommissioningid").ToString))
            strSqlList.Add(String.Format("UPDATE CONTAINERCOMMISSIONING C SET C.ISUSE =1,C.ISTRANSFER=0 WHERE C.CONTAINERCOMMISSIONINGID='{0}'", dr("containercommissioningid").ToString))
            strSqlList.Add(String.Format(" update containercurrentinfo cu set CU.Iscommissioning = 0,cu.commissioningnum = 0 WHERE CU.CONTAINERID = '{0}' ", dr("CONTAINERID").ToString))
            '如果有报废，需要还原数量
            If Val(dr("lossqty").ToString) > 0 Then
                Dim changeQtyDt = DatabaseHelper.Query_ReturnDatTable(String.Format(" select * from CheckQtyChangeRecord ch where ch.containercheckinfoid='{0}' and ch.scrapsourcetype=5 ", dr("containercommissioningid").ToString))
                If changeQtyDt.Rows.Count > 0 Then
                    For Each changeRow In changeQtyDt.Rows
                        strSqlList.Add(String.Format(" update container c set c.qty=c.qty+{0},c.status=1 where c.containerid='{1}' ", -Val(changeRow("qty").ToString), changeRow("containerid").ToString))
                        strSqlList.Add(String.Format(updateChangeQtySql, dr("containercommissioningid").ToString))
                        'strSqlList.Add(String.Format(changeQtySql, changeRow("containerid").ToString, -Val(changeRow("qty").ToString), para("EmployeeID"), dr("cuworkflowstepid").ToString)) '记录撤销的数量变更  暂不启用

                        If dr("containertype").ToString <> "3" Then
                            strSqlList.Add(String.Format(" update containercurrentinfo cci set cci.qty=cci.qty+{0} ,cci.containerstatus=1 where cci.containerid='{1}' ", -Val(changeRow("qty").ToString), changeRow("containerid").ToString))
                        End If
                    Next
                    If dr("containertype").ToString = "3" Then '子序号情况,把主卡变更
                        strSqlList.Add(String.Format(" update container c set c.qty=c.qty+{0} , c.status=1 where c.containerid='{1}' ", changeQtyDt.Rows.Count, dr("containerid").ToString))
                        strSqlList.Add(String.Format(" update containercurrentinfo cci set cci.qty=cci.qty+{0} ,cci.containerstatus=1 where cci.containerid='{1}' ", changeQtyDt.Rows.Count, dr("containerid").ToString))
                    End If

                End If
            End If

            DatabaseHelper.ExecuteSqlTran(strSqlList)
            LogHelp.WriteOrcale("外委撤销" + "@^" + "批次：" + dr("ContainerID").ToString + "外委撤销成功" + "@^" + "开始工序：结束工序" + dr("workflowstepid").ToString + ":" + dr("inworkflowstepid").ToString + "@^" + Convert.ToString(Guid.NewGuid()) + "@^" + para("FullName")) 'add:Wangjh 写入数据库   

        Next


        re.Result = True

        Return re

    End Function

    ''' <summary>
    ''' 获取批次已经外委的工序信息
    ''' </summary>
    ''' <param name="containerid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetCommissionProcess(ByRef containerid As String) As DataTable Implements IBusiness.uLMSCommissioningIBusiness.GetCommissionProcess
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CCG.CONTAINERCOMMISSIONINGID,CCG.CONTAINERID,CCG.WORKFLOWSTEPID,CCG.INWORKFLOWSTEPID,")
        strsql.AppendLine("CCG.OUTCUSTOMERID,CCG.OUTDATE,WS.SEQUENCE FIRSTSEQUENCE,WS_.SEQUENCE LASTSEQUENCE,WS.workflowstepname firststepname,ws_.workflowstepname laststepname ")
        strsql.AppendLine("FROM CONTAINERCOMMISSIONING CCG")
        strsql.AppendLine("LEFT JOIN (SELECT MAX(CC.Proposedate) Proposedate,CC.CONTAINERID FROM CONTAINERCOMMISSIONING CC")
        strsql.AppendFormat("  WHERE {0} AND CC.ISUSE=0 GROUP BY CC.CONTAINERID) A ON A.CONTAINERID = CCG.CONTAINERID ", containerid)
        strsql.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID=CCG.WORKFLOWSTEPID")
        strsql.AppendLine("LEFT JOIN WORKFLOWSTEP WS_ ON WS_.WORKFLOWSTEPID=CCG.INWORKFLOWSTEPID")
        strsql.AppendLine(" WHERE A.Proposedate = CCG.Proposedate AND CCG.CONTAINERID = A.CONTAINERID AND CCG.ISUSE = 0")
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
    End Function

    ''' <summary>
    ''' 更新调度信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function UpdateContainerCommissioning(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSCommissioningIBusiness.UpdateContainerCommissioning
        Dim strsql As New StringBuilder
        strsql.AppendFormat("UPDATE CONTAINERCOMMISSIONING CCG SET CCG.ACCEPTANCEQTY={0},", para("AcceptQty"))
        strsql.AppendFormat("CCG.ISTRANSFER=0,CCG.LOSSQTY={0},", para("LossQty"))
        strsql.AppendFormat("CCG.INEMPLOYEEID='{0}',", para("InEmployeeID"))
        If para.ContainsKey("WipQty") Then
            strsql.AppendFormat("CCG.OUTQTY='{0}',", para("WipQty"))
        End If
        strsql.AppendFormat("CCG.INDATE=SYSDATE WHERE CCG.CONTAINERCOMMISSIONINGID='{0}'", para("ContainerCommissioningID"))
        If DatabaseHelper.ExecuteSql(strsql.ToString) > 0 Then
            Return True
        End If
        Return False
    End Function

    ''' <summary>
    ''' 根据ContainerID获取已经调度的外委信息
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetHadCommissioningInfo(ByVal containerID As String) As DataTable Implements IBusiness.uLMSCommissioningIBusiness.GetHadCommissioningInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CCG.CONTAINERCOMMISSIONINGID,CCG.ACCEPTANCEQTY,CCG.LOSSQTY,CCG.OUTDATE,CCG.INDATE,EM1.FULLNAME OUTEMPLOYEENAME,")
        strsql.AppendLine("EM2.FULLNAME INEMPLOYEENAME,CCG.WORKFLOWSTEPID,CCG.INWORKFLOWSTEPID,CCG.OUTCUSTOMERID,C.CONTAINERNAME")
        strsql.AppendLine("FROM CONTAINERCOMMISSIONING CCG")
        strsql.AppendLine("LEFT JOIN CONTAINER C ON C.CONTAINERID = CCG.CONTAINERID")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM1 ON EM1.EMPLOYEEID = CCG.OUTEMPLOYEEID")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM2 ON EM2.EMPLOYEEID = CCG.INEMPLOYEEID")
        strsql.AppendFormat(" WHERE CCG.INEMPLOYEEID IS NOT NULL AND CCG.ISUSE = 0 AND CCG.CONTAINERID IN ('{0}') ORDER BY C.CONTAINERNAME", containerID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
    End Function

    ''' <summary>
    ''' 获取外委厂家信息
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetCustomerInfo() As DataTable Implements IBusiness.uLMSCommissioningIBusiness.GetCustomerInfo
        Dim strsql As String = " SELECT C.CUSTOMERID, C.CUSTOMERNAME FROM CUSTOMER C ORDER BY C.CUSTOMERID"
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 添加固定外委信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddFixedCommissionInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSCommissioningIBusiness.AddFixedCommissionInfo
        AddFixedCommissionInfo = False
        Dim dto As ContainerCommissioningDTO
        'add:Wagnjh 20180905 增加取消的自动外委判断
        para("CheckedWorkflowStepID") = para("WorkflowStepID")
        If m_ContainerShip.GetCancleAutoShipOrComission(para).Rows.Count > 0 Then
            AddFixedCommissionInfo = True
            Exit Function
        End If
        '
        Dim strInWorkflowStepID = ""
        GetInWorkflowStepIDFunction(para, strInWorkflowStepID)
        '固定外委不插入外委人信息
        dto = New ContainerCommissioningDTO With
              {.ContainerID = para("ContainerID"),
               .ContainerName = para("ContainerName"),
               .FactoryID = para("FactoryID"),
               .WorkflowStepID = para("WorkflowStepID"),
               .ContainerCommissioningID = Guid.NewGuid.ToString,
               .OutCustomerID = "",
               .InWorkflowStepID = strInWorkflowStepID,
               .OutEmployeeID = "",
               .OutQty = Integer.Parse(para("OutQty"))
              }
         
        If AddContainerCommissioning(dto) Then
            UpdateCommissioningNum(dto.ContainerID, 1, 1) '固定外委更新批次在服务总表的外委标识
            Return True
        End If
    End Function

    ''' <summary>
    ''' 获取InWorkflowStepID方法
    ''' </summary>
    ''' <remarks></remarks>
    Sub GetInWorkflowStepIDFunction(ByVal para As Dictionary(Of String, String), ByRef strInWorkflowStepID As String)
        Dim WorkflowID As String = para("WorkflowID")
        Dim sequence As String = para("Sequence")
        Dim nextWorkFlowStepID = m_ContainerShip.GetNextWorkFlowID(WorkflowID, Convert.ToInt64(sequence)) 'update:Wangjh 20181206 取消+ 1，传入值时已经+1了
        para("CheckedWorkflowStepID") = nextWorkFlowStepID 'add:Wangjh 20180905 增加取消的自动外委工序查询
        Dim newpara As Dictionary(Of String, String)
        If m_CommonObj.GetControlCodeByWorkflowStepID(nextWorkFlowStepID) = "LY05" And m_ContainerShip.GetCancleAutoShipOrComission(para).Rows.Count = 0 Then 'add:Wangjh 20180905 增加取消的自动外委判断
            newpara = New Dictionary(Of String, String)
            newpara.Add("WorkflowID", WorkflowID)
            newpara.Add("Sequence", Convert.ToInt64(sequence) + 1)
            newpara.Add("ContainerID", para("ContainerID"))
            GetInWorkflowStepIDFunction(newpara, strInWorkflowStepID)
        Else
            strInWorkflowStepID = nextWorkFlowStepID
        End If
    End Sub

    ''' <summary>
    ''' 获取当前外委信息 modify by tianFW  将单件单卡合并查询 2018年10月18日
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCurrentCommissionInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSCommissioningIBusiness.GetCurrentCommissionInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT PRODUCTDESCRIPTION,CONTAINERNO,PRODUCTNAME,sum(OUTQTY) OUTQTY,sum(QTY) QTY,CUSTOMERNAME,OUTDATE FROM(")
        strsql.AppendLine("SELECT CU.PRODUCTDESCRIPTION,CU.CONTAINERNO,CU.PRODUCTNAME,CCS.OUTQTY,CU.QTY,CR.CUSTOMERNAME,to_char(CCS.OUTDATE,'yyyy/MM/dd hh24:Mi') OUTDATE")
        strsql.AppendLine("FROM CONTAINERCURRENTINFO CU ")
        strsql.AppendLine("LEFT JOIN CONTAINERCOMMISSIONING CCS ON CCS.CONTAINERID = CU.CONTAINERID")
        strsql.AppendLine("LEFT JOIN CUSTOMER CR ON CR.CUSTOMERID = CCS.OUTCUSTOMERID")
        strsql.AppendFormat(" WHERE CU.ISCOMMISSIONING=1 AND CU.FACTORYID='{0}' AND CCS.ISUSE=0)", para("FactoryID"))
        strsql.AppendLine("GROUP BY PRODUCTDESCRIPTION,CONTAINERNO,PRODUCTNAME,OUTQTY,QTY,CUSTOMERNAME,OUTDATE")
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 获取批次当前序的外委信息 create by tianFW 2018年10月11日
    ''' </summary>
    ''' <param name="containerIDs">批次ID</param>
    ''' <param name="workflowstepID">当前工序ID</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerCurrnetCommInfo(ByVal containerIDs As String, ByVal workflowstepID As String) As DataTable Implements uLMSCommissioningIBusiness.GetContainerCurrnetCommInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT EM1.FULLNAME OUTFULLNAME, EM2.FULLNAME INFULLNAME,'' CONTAINERNAME,cmm.outdate,cmm.indate,cmm.containerid,")
        strsql.AppendLine("WS1.WORKFLOWSTEPNAME||' : '||WS3.WORKFLOWSTEPNAME WORKFLOWSTEPNAME,cmm.containercommissioningid,cmm.outdate TXNDATE")
        strsql.AppendLine("FROM containercommissioning cmm")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM1 ON EM1.EMPLOYEEID = cmm.OutEmployeeID")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM2 ON EM2.EMPLOYEEID = cmm.InEmployeeID")
        strsql.AppendLine("LEFT JOIN WORKFLOWSTEP WS1 ON WS1.WORKFLOWSTEPID = cmm.workflowstepid")
        strsql.AppendLine("LEFT JOIN WORKFLOWSTEP WS2 ON WS2.WORKFLOWSTEPID = cmm.inworkflowstepid")
        strsql.AppendLine("LEFT JOIN workflowstep ws3 ON ws3.workflowid = ws2.workflowid AND ws3.sequence = ws2.sequence-1")
        strsql.AppendLine("LEFT JOIN (SELECT ws.workflowid,ws.workflowstepid,ws.sequence,ws.workflowstepname ")
        strsql.AppendFormat(" FROM Workflowstep ws WHERE ws.workflowstepid='{0}' ", workflowstepID)
        strsql.AppendLine(") K ON ws1.workflowid = k.workflowid AND ws2.workflowid = k.workflowid")
        strsql.AppendLine("WHERE cmm.isuse = 0 AND ws1.sequence<=k.sequence AND ws2.sequence>k.sequence")
        strsql.AppendFormat(" AND cmm.CONTAINERID IN ({0})", containerIDs)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 更新批次的外委标识
    ''' </summary>
    ''' <param name="containerID">批次ID</param>
    ''' <param name="type">1 当前序 2 其他序</param>
    ''' <param name="value">0 无 1 有,当此为0时，type值不使用，随便传值</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateCommissioningNum(ByVal containerID As String, ByVal type As Integer, ByVal value As Integer) As Boolean Implements uLMSCommissioningIBusiness.UpdateCommissioningNum
        UpdateCommissioningNum = False
        Dim strsql As New StringBuilder
        strsql.AppendLine("UPDATE CONTAINERCURRENTINFO CU SET")
        If value = 0 Then '置为0，则就是删除或者调度回来，此时应该两者都置为0
            strsql.AppendFormat(" CU.Iscommissioning = 0,cu.commissioningnum = 0 WHERE CU.CONTAINERID = '{0}'", containerID)
        Else
            If type = 2 Then '如果外委的没有当前序，则直接将cu.commissioningnum置为1
                strsql.AppendFormat(" cu.commissioningnum = 1 WHERE CU.CONTAINERID = '{0}'", containerID)
            ElseIf type = 1 Then '如果外委的有当前序，则两者都置为1
                strsql.AppendFormat(" CU.Iscommissioning = 1,cu.commissioningnum = 1 WHERE CU.CONTAINERID = '{0}'", containerID)
            End If
        End If
        If Not DatabaseHelper.ExecuteSql(strsql.ToString()) > 0 Then
            Exit Function
        End If
        UpdateCommissioningNum = True
    End Function

#Region "外委转出、临时外委申请"
    ''' <summary>
    ''' 获取外委转出批次主数据 add by zhangrj 2022-11-24
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerCommissioningOutList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSCommissioningIBusiness.GetContainerCommissioningOutList
        Dim result As New uMESProcessResult
        Try
            Dim strSql = <string>
SELECT cci.mfgorderid,cci.mfgordername,cci.containerno,cci.togetherflag,cci.containertype,
DECODE(cci.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME
,cci.workflowstepid,cci.workflowstepname,cci.plannedstartdate,cci.plannedcompletiondate,
  (nvl(cci.Workflowroute,cci.productname)||'/'||cci.containerno) AS ProductBatch,cci.productdescription productdesc,
        cci.workflowid,cci.workflowname,sum(cci.qty) wipqty,cci.productid,cci.planqty
        from containercurrentinfo cci 
        left join containercommissioning cc ON cc.containerid = cci.containerid AND cc.isuse = 0 
        where cci.containerstatus=1 AND cci.iscommissioning = 1 AND cci.isinstore = 0 
        AND cci.splitnum = 0 AND cci.factoryid = '{0}' 
                     </string>.Value
            strSql = String.Format(strSql, para("FactoryID"))

            If para.Keys.Contains("ScanContainerName") Then
                If Not String.IsNullOrWhiteSpace(para("ScanContainerName")) Then
                    strSql += " AND ((cci.containertype <> '7' and cci.containername = '" + para("ScanContainerName") + "')"
                    strSql += " or (cci.containertype = '7' and cci.togetherflag = '" + para("ScanContainerName") + "'))"
                End If
            End If
            If para.Keys.Contains("ScanOrderName") Then
                If Not String.IsNullOrWhiteSpace(para("ScanOrderName")) Then
                    strSql += " AND cci.mfgordername = '" + para("ScanOrderName") + "'"
                End If
            End If
            If para.Keys.Contains("ProductName") Then
                If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
                    strSql += " AND cci.productname like '%" + para("ProductName") + "%'"
                End If
            End If
            If para.Keys.Contains("ContainerName") Then
                If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                    strSql += " AND cci.containername like '%" + para("ContainerName") + "%'"
                End If
            End If
            If para.Keys.Contains("StepName") Then
                If Not String.IsNullOrWhiteSpace(para("StepName")) Then
                    strSql += " AND cci.workflowstepname like '%" + para("StepName") + "%'"
                End If
            End If
            If para.Keys.Contains("Status") Then
                If Not String.IsNullOrWhiteSpace(para("Status")) Then
                    If para("Status") = "1" Then '待转出
                        strSql += " AND cc.outdate IS NULL"
                    ElseIf para("Status") = "2" Then '待审核
                        strSql += " AND cc.outdate IS NOT NULL AND cc.outsourcoutno IS NULL"
                    ElseIf para("Status") = "3" Then '已转出
                        strSql += " AND cc.outdate IS NOT NULL AND cc.outsourcoutno IS NOT NULL AND cc.IsSendScm = 1"
                    End If
                End If
            End If
            strSql += <string>
         group by cci.mfgorderid,cci.mfgordername,cci.containerno,cci.togetherflag,cci.containertype,cci.productdescription,
        cci.workflowstepid,cci.workflowstepname,cci.workflowstepsequence,cci.plannedstartdate,cci.plannedcompletiondate,
cci.workflowid,cci.workflowname,cci.productname,cci.qty,cci.productid,cci.Workflowroute,cci.planqty                        
                     </string>.Value
            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(para("PageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
            paraPage.strSQL = strSql.ToString
            result = DatabaseHelper.RunProcedureForPage(paraPage)
        Catch ex As Exception
            result.Result = False
            result.Message = "出错了,原因：" + ex.Message
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 获取外委转出批次详细数据 add by zhangrj 2022-11-24
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerCommissioningOutDetailList(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSCommissioningIBusiness.GetContainerCommissioningOutDetailList
        Dim strSql = <string>select cci.containerid,cci.mfgorderid,cci.mfgordername,cci.containerno,cci.containername,cci.containertype,DECODE(cci.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME
 ,cci.productdescription,cci.workflowstepid,cci.workflowstepname,cci.plannedstartdate,cci.plannedcompletiondate,cci.specno,
cci.workflowid,cci.workflowname,cci.productname,cci.qty,cci.productid,cci.workflowstepsequence sequence
from containercurrentinfo cci 
where cci.containerstatus=1 AND cci.iscommissioning = 1 AND cci.splitnum = 0 AND cci.isinstore = 0 
</string>.Value

        If para.ContainsKey("OrderNo") Then
            If Not String.IsNullOrWhiteSpace(para("OrderNo")) Then
                strSql += String.Format(" and cci.mfgordername = '{0}' ", para("OrderNo"))
            End If
        End If
        If para.ContainsKey("ProductId") Then
            If Not String.IsNullOrWhiteSpace(para("ProductId")) Then
                strSql += String.Format(" and cci.productid = '{0}' ", para("ProductId"))
            End If
        End If

        If para.ContainsKey("Togetherflag") Then
            If Not String.IsNullOrWhiteSpace(para("Togetherflag")) Then
                strSql += String.Format(" and cci.togetherflag = '{0}' ", para("Togetherflag"))
            End If
        End If

        If para.ContainsKey("ContainerNo") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                strSql += String.Format(" and cci.ContainerNo = '{0}' ", para("ContainerNo"))
            End If
        End If

        If para.ContainsKey("WorkflowID") Then
            If Not String.IsNullOrWhiteSpace(para("WorkflowID")) Then
                strSql += String.Format(" and cci.workflowid = '{0}' ", para("WorkflowID"))
            End If
        End If

        If para.ContainsKey("WorkflowStepId") Then
            If Not String.IsNullOrWhiteSpace(para("WorkflowStepId")) Then
                strSql += String.Format(" and cci.workflowstepid = '{0}' ", para("WorkflowStepId"))
            End If
        End If


        If para.ContainsKey("FactoryID") Then
            If Not String.IsNullOrWhiteSpace(para("FactoryID")) Then
                strSql += String.Format(" and cci.factoryid = '{0}' ", para("FactoryID"))
            End If
        End If

        strSql += " order by cci.containerid "

        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取临时外委批次主数据 add by zhangrj 2022-11-30
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerCommissioningTempList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSCommissioningIBusiness.GetContainerCommissioningTempList
        Dim result As New uMESProcessResult
        Try
            Dim strSql = <string>
 SELECT cci.mfgorderid,cci.mfgordername,cci.containerno,cci.togetherflag,cci.containertype,
DECODE(cci.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME
,cci.workflowstepid,cci.workflowstepname,cci.plannedstartdate,cci.plannedcompletiondate,
(nvl(cci.Workflowroute,cci.productname)||'/'||cci.containerno) AS ProductBatch,cci.productdescription productdesc,
cci.workflowid,cci.workflowname,sum(cci.qty) wipqty,cci.productid,cci.planqty
from containercurrentinfo cci 
where cci.containerstatus=1 AND cci.iscommissioning = 0 AND cci.isinstore = 0 AND cci.splitnum = 0 AND cci.factoryid = '{0}' 
                     </string>.Value
            strSql = String.Format(strSql, para("FactoryID"))

            If para.Keys.Contains("ScanContainerName") Then
                If Not String.IsNullOrWhiteSpace(para("ScanContainerName")) Then
                    strSql += " AND ((cci.containertype <> '7' and cci.containername = '" + para("ScanContainerName") + "')"
                    strSql += " or (cci.containertype = '7' and cci.togetherflag = '" + para("ScanContainerName") + "'))"
                End If
            End If
            If para.Keys.Contains("ScanOrderName") Then
                If Not String.IsNullOrWhiteSpace(para("ScanOrderName")) Then
                    strSql += " AND cci.mfgordername = '" + para("ScanOrderName") + "'"
                End If
            End If
            If para.Keys.Contains("ProductName") Then
                If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
                    strSql += " AND cci.productname like '%" + para("ProductName") + "%'"
                End If
            End If
            If para.Keys.Contains("ContainerName") Then
                If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                    strSql += " AND cci.containername like '%" + para("ContainerName") + "%'"
                End If
            End If
            If para.Keys.Contains("StepName") Then
                If Not String.IsNullOrWhiteSpace(para("StepName")) Then
                    strSql += " AND cci.workflowstepname like '%" + para("StepName") + "%'"
                End If
            End If
            strSql += <string>
         group by cci.mfgorderid,cci.mfgordername,cci.containerno,cci.togetherflag,cci.containertype,cci.productdescription,
        cci.workflowstepid,cci.workflowstepname,cci.workflowstepsequence,cci.plannedstartdate,cci.plannedcompletiondate,
cci.workflowid,cci.workflowname,cci.productname,cci.qty,cci.productid,cci.Workflowroute,cci.productdescription,cci.planqty                        
                     </string>.Value
            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(para("PageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
            paraPage.strSQL = strSql.ToString
            result = DatabaseHelper.RunProcedureForPage(paraPage)
        Catch ex As Exception
            result.Result = False
            result.Message = "出错了,原因：" + ex.Message
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 获取临时外委批次详细数据 add by zhangrj 2022-11-30
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerCommissioningTempDetailList(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSCommissioningIBusiness.GetContainerCommissioningTempDetailList
        Dim strSql = <string>select cci.containerid,cci.mfgorderid,cci.mfgordername,cci.containerno,cci.containername,cci.containertype,DECODE(cci.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME
 ,cci.productdescription,cci.workflowstepid,cci.workflowstepname,cci.plannedstartdate,cci.plannedcompletiondate,cci.specno,
cci.workflowid,cci.workflowname,cci.productname,cci.qty wipqty,cci.productid,cci.workflowstepsequence sequence
from containercurrentinfo cci 
where cci.containerstatus=1 AND cci.iscommissioning = 0 AND cci.splitnum = 0 AND cci.isinstore = 0 
</string>.Value

        If para.ContainsKey("OrderNo") Then
            If Not String.IsNullOrWhiteSpace(para("OrderNo")) Then
                strSql += String.Format(" and cci.mfgordername = '{0}' ", para("OrderNo"))
            End If
        End If
        If para.ContainsKey("ProductId") Then
            If Not String.IsNullOrWhiteSpace(para("ProductId")) Then
                strSql += String.Format(" and cci.productid = '{0}' ", para("ProductId"))
            End If
        End If

        If para.ContainsKey("Togetherflag") Then
            If Not String.IsNullOrWhiteSpace(para("Togetherflag")) Then
                strSql += String.Format(" and cci.togetherflag = '{0}' ", para("Togetherflag"))
            End If
        End If

        If para.ContainsKey("ContainerNo") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                strSql += String.Format(" and cci.ContainerNo = '{0}' ", para("ContainerNo"))
            End If
        End If

        If para.ContainsKey("WorkflowID") Then
            If Not String.IsNullOrWhiteSpace(para("WorkflowID")) Then
                strSql += String.Format(" and cci.workflowid = '{0}' ", para("WorkflowID"))
            End If
        End If

        If para.ContainsKey("WorkflowStepId") Then
            If Not String.IsNullOrWhiteSpace(para("WorkflowStepId")) Then
                strSql += String.Format(" and cci.workflowstepid = '{0}' ", para("WorkflowStepId"))
            End If
        End If


        If para.ContainsKey("FactoryID") Then
            If Not String.IsNullOrWhiteSpace(para("FactoryID")) Then
                strSql += String.Format(" and cci.factoryid = '{0}' ", para("FactoryID"))
            End If
        End If

        strSql += " order by cci.containerid "

        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 保存临时外委数据 add by zhangrj 2022-11-30
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="containerDT"></param>
    ''' <param name="stepDT"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveContainerTempCommiData(ByVal para As Dictionary(Of String, String), ByVal containerDT As DataTable, ByVal stepDT As DataTable) As Boolean Implements uLMSCommissioningIBusiness.SaveContainerTempCommiData
        Dim sqlList As New ArrayList
        Dim sql As New StringBuilder
        For Each drCon As DataRow In containerDT.Rows
            sql.Clear()
            Dim containercommissioningID As String = Guid.NewGuid.ToString()
            sql.AppendLine("INSERT INTO CONTAINERCOMMISSIONING(CONTAINERID,CONTAINERNAME,FACTORYID,OUTQTY,WORKFLOWSTEPID,INWORKFLOWSTEPID,CONTAINERCOMMISSIONINGID,ISUSE,ISTRANSFER,proposerID,proposerName,proposedate,PurchaseOrganizeName,PurchaseOrganizeDesc) VALUES(")
            sql.AppendFormat("'{0}',", drCon("CONTAINERID").ToString()) '批次ID
            sql.AppendFormat("'{0}',", drCon("CONTAINERNAME").ToString()) '批次号
            sql.AppendFormat("'{0}',", para("FactoryID").ToString()) '申请工厂
            sql.AppendFormat("'{0}',", drCon("WIPQTY").ToString()) '外委数
            sql.AppendFormat("'{0}',", para("WorkflowStepID").ToString()) '外委工序
            sql.AppendFormat("'{0}',", para("InWorkflowStepID").ToString()) '转入工序
            sql.AppendFormat("'{0}',0,0,", containercommissioningID)
            sql.AppendFormat("'{0}',", para("EmployeeID").ToString()) '申请人ID
            sql.AppendFormat("'{0}',sysdate,", para("EmployeeName").ToString()) '申请人名
            sql.AppendFormat("'{0}',", para("PurchaseCode").ToString()) '采购组码
            sql.AppendFormat("'{0}')", para("PurchaseName").ToString()) '采购组名
            sqlList.Add(sql.ToString())
            '添加外委明细
            For Each drStep As DataRow In stepDT.Rows
                sql.Clear()
                sql.AppendLine("INSERT INTO ContainerCommiDetail(Containercommidetailid,Containercommissioningid,Containerid,Workflowid,Workflowstepid,Workflowstepname,Isuse) VALUES(")
                sql.AppendFormat("'{0}',", Guid.NewGuid.ToString()) 'ID
                sql.AppendFormat("'{0}',", containercommissioningID) '外委主表ID
                sql.AppendFormat("'{0}',", drCon("CONTAINERID").ToString()) '批次ID
                sql.AppendFormat("'{0}',", drCon("WorkflowID").ToString()) '工艺ID
                sql.AppendFormat("'{0}',", drStep("WorkflowStepID").ToString()) '外委工序ID
                sql.AppendFormat("'{0}',0)", drStep("WorkflowStepName").ToString()) '外委工序名
                sqlList.Add(sql.ToString())
            Next
        Next
        DatabaseHelper.ExecuteSqlTran(sqlList)
        For Each item As DataRow In containerDT.Rows
            UpdateCommissioningNum(item("CONTAINERID").ToString(), Integer.Parse(para("Type")), 1) '固定外委更新批次在服务总表的外委标识
        Next
        Return True
    End Function

#End Region

#Region "自检自分 add by zhangrj 20230609"
    ''' <summary>
    ''' 验证是否有效的自检自分外委
    ''' </summary>
    ''' <param name="workflowStepID"></param>
    ''' <param name="inWorkflowStepID"></param>
    ''' <param name="msg"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsValidSelfCheckCommission(ByVal workflowStepID As String, ByVal inWorkflowStepID As String, ByRef msg As String) As Boolean Implements uLMSCommissioningIBusiness.IsValidSelfCheckCommission
        Dim strsql = <string>select ws.workflowid,ws.workflowstepid,ws.workflowstepname,NVL(ws.controlcode,s.controlcode) controlcode
from workflowstep ws
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
left join workflowstep ws1 on ws1.workflowstepid='{0}'
left join workflowstep ws2 on ws2.workflowstepid='{1}'
where ws.workflowid=ws1.workflowid</string>.Value
        strsql += " and ws.sequence>=ws1.sequence and ws.sequence<ws2.sequence "
        Dim stepDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(strsql, workflowStepID, inWorkflowStepID))
        '只有一个外委序，不验证
        If stepDt.Rows.Count = 1 Then
            Return True
        End If
        Dim res = True
        For Each dr As DataRow In stepDt.Rows
            If String.IsNullOrWhiteSpace(dr("controlcode").ToString()) Then
                msg += String.Format("{0} ", dr("workflowstepname").ToString)
                res = False
            End If
        Next
        If Not String.IsNullOrWhiteSpace(msg) Then
            msg = String.Format("工序：{0}是专检序，不能批量操作", msg)
        End If
        Return res
    End Function

    ''' <summary>
    ''' 填充自检自分报工记录 add by zhangrj 20230609
    ''' </summary>
    ''' <param name="dic"></param>
    ''' <param name="m_UserInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function FillSelfCheckReportData(ByVal dic As Dictionary(Of String, String), ByVal m_UserInfo As CurrentUserInfo) As Boolean Implements uLMSCommissioningIBusiness.FillSelfCheckReportData
        Dim res = False
        Dim sqlStringList As New List(Of Object())
        Dim strSql = String.Format(<string>
                                       SELECT 1
                                       FROM workflowstep ws
                                       LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
                                       LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
                                       WHERE ws.workflowstepid = '{0}' AND nvl(ws.controlcode,s.controlcode) IS NULL
                                       AND NOT EXISTS(SELECT 1 FROM containerstepworkreport csw 
                                       WHERE csw.containerid = '{1}' AND csw.workflowstepid = '{0}' AND csw.isuse = 0)
                     </string>.Value, dic("WorkflowStepID"), dic("ContainerID"))
        Dim dtres = DatabaseHelper.Query_ReturnDatTable(strSql)
        '如果当前序是专检序，且没有报工记录
        If Not DatabaseHelper.GetSingle(strSql) Is Nothing Then
            Dim dto = New ContainerStepWorkReportDTO()
            dto.ContainerID = dic("ContainerID")
            dto.WorkflowstepID = dic("WorkflowStepID")
            dto.Qty = Integer.Parse(dic("Qty"))
            dto.WorkerID = m_UserInfo.EmployeeID
            dto.FactoryID = m_UserInfo.FactoryID
            dto.WorkCenterID = m_UserInfo.WorkCenterID
            dto.ResourceID = ""
            dto.CheckerID = ""
            dto.FirstContainerNo = ""
            dto.KeyProcessBatchNo = ""
            dto.TeamID = m_UserInfo.TeamID
            dto.CheckType = 2
            dto.ContainerStepDispatchID = ""
            dto.ContainerStepWorkReportID = Guid.NewGuid().ToString()
            sqlStringList.Add(FillContainerStepWorkReportInfoTrans(dto, "")) '插入报工记录
            sqlStringList.Add(FillUpdateStepCheckNum(dic("ContainerID"), 2)) '更新批次报工类型
            DatabaseHelper.ExecuteSqlTran(sqlStringList)
            res = True
        End If
        Return res
    End Function

    Public Function FillUpdateStepCheckNum(ByVal containerIDs As String, ByVal Type As Integer) As Object()
        Dim strsql As New StringBuilder
        strsql.AppendLine("UPDATE containercurrentinfo cu SET")
        If Type = 1 Then
            strsql.AppendLine("cu.firstchecknum = 1")
        Else
            strsql.AppendLine("cu.stepchecknum = 1")
        End If
        strsql.AppendFormat(" WHERE cu.containerid in ('{0}')", containerIDs)
        Return New Object() {strsql.ToString, Nothing}

    End Function

    Public Function FillContainerStepWorkReportInfoTrans(ByVal entity As ContainerStepWorkReportDTO, ByVal value As String) As Object()
        Dim strsql As String =
        <string>
                INSERT INTO CONTAINERSTEPWORKREPORT(CONTAINERID,FACTORYID,WORKCENTERID,TEAMID,WORKFLOWSTEPID,RESOURCEID,WORKERID,CHECKERID,QTY,CHECKTYPE,CONTAINERSTEPDISPATCHID,
                CONTAINERSTEPWORKREPORTID,CREATEDATE,ISUSE,SELFCHECKOUTOFTOLERANCEQTY,SELFCHECKLOSSQTY,SELFCHECKQUALIFIEDQTY,ISTRANSFER,Qty2,UOM2ID,SurplusEmployeeID,STEPINFOID,Specimenqty,Specimenlossqty,
                reasontype,reasondesc,firstcheckcontainerno,keyprocessbatchno,notes)
                VALUES(:Containerid,:Factoryid,:Workcenterid,:Teamid,:Workflowstepid,:Resourceid,:Workerid,:Checkerid,:Qty,:Checktype,:Containerstepdispatchid,
                :Containerstepworkreportid,:Createdate,:Isuse,:SELFCHECKOUTOFTOLERANCEQTY,:SELFCHECKLOSSQTY,:SELFCHECKQUALIFIEDQTY,:ISTRANSFER,:Qty2,:UOM2ID,
                :SurplusEmployeeID,:STEPINFOID,:Specimenqty,:Specimenlossqty,:reasontype,:reasondesc,:firstcheckcontainerno,:keyprocessbatchno,:notes)
        </string>
        Dim insertpar(28) As OracleParameter
        insertpar(0) = DatabaseHelper.NewParameter(":Containerid", entity.ContainerID, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":Factoryid", entity.FactoryID, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":Workcenterid", entity.WorkCenterID, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":Teamid", entity.TeamID, OracleType.VarChar)
        insertpar(4) = DatabaseHelper.NewParameter(":Workflowstepid", entity.WorkflowstepID, OracleType.VarChar)
        insertpar(5) = DatabaseHelper.NewParameter(":Resourceid", entity.ResourceID, OracleType.VarChar)
        insertpar(6) = DatabaseHelper.NewParameter(":Workerid", entity.WorkerID, OracleType.VarChar)
        insertpar(7) = DatabaseHelper.NewParameter(":Checkerid", entity.CheckerID, OracleType.VarChar)
        insertpar(8) = DatabaseHelper.NewParameter(":Qty", entity.Qty, OracleType.Number)
        insertpar(9) = DatabaseHelper.NewParameter(":Checktype", entity.CheckType, OracleType.Number)
        insertpar(10) = DatabaseHelper.NewParameter(":Containerstepdispatchid", entity.ContainerStepDispatchID, OracleType.VarChar)
        insertpar(11) = DatabaseHelper.NewParameter(":Containerstepworkreportid", entity.ContainerStepWorkReportID, OracleType.VarChar)
        If entity.CreateDate = DateTime.Parse("0001/1/1") Then
            insertpar(12) = DatabaseHelper.NewParameter(":Createdate", System.DateTime.Now, OracleType.DateTime)
        Else
            insertpar(12) = DatabaseHelper.NewParameter(":Createdate", entity.CreateDate, OracleType.DateTime)
        End If
        insertpar(13) = DatabaseHelper.NewParameter(":Isuse", 0, OracleType.Number)
        '
        insertpar(14) = DatabaseHelper.NewParameter(":SELFCHECKOUTOFTOLERANCEQTY", entity.SelfCheckOutofToleranceQty, OracleType.Number)
        insertpar(15) = DatabaseHelper.NewParameter(":SELFCHECKLOSSQTY", entity.SelfCheckLossQty, OracleType.Number)
        insertpar(16) = DatabaseHelper.NewParameter(":SELFCHECKQUALIFIEDQTY", entity.SeleCheckQualifiedQty, OracleType.Number)
        If value = "split" Then
            insertpar(17) = DatabaseHelper.NewParameter(":ISTRANSFER", 1, OracleType.Number)
        Else
            insertpar(17) = DatabaseHelper.NewParameter(":ISTRANSFER", 0, OracleType.Number)
        End If
        If Not entity.UOM2ID Is Nothing And entity.Qty2 <> 0 Then
            insertpar(18) = DatabaseHelper.NewParameter(":Qty2", entity.Qty2, OracleType.Number)
            insertpar(19) = DatabaseHelper.NewParameter(":UOM2ID", entity.UOM2ID, OracleType.VarChar)
        Else
            insertpar(18) = DatabaseHelper.NewParameter(":Qty2", DBNull.Value, OracleType.Number)
            insertpar(19) = DatabaseHelper.NewParameter(":UOM2ID", DBNull.Value, OracleType.VarChar)
        End If
        If Not entity.SurplusEmployeeID Is Nothing Then
            insertpar(20) = DatabaseHelper.NewParameter(":SurplusEmployeeID", entity.SurplusEmployeeID, OracleType.VarChar)
        Else
            insertpar(20) = DatabaseHelper.NewParameter(":SurplusEmployeeID", DBNull.Value, OracleType.VarChar)
        End If
        If Not entity.UOM2ID Is Nothing And entity.Qty2 <> 0 Then
            insertpar(18) = DatabaseHelper.NewParameter(":Qty2", entity.Qty2, OracleType.Number)
            insertpar(19) = DatabaseHelper.NewParameter(":UOM2ID", entity.UOM2ID, OracleType.VarChar)
        Else
            insertpar(18) = DatabaseHelper.NewParameter(":Qty2", DBNull.Value, OracleType.Number)
            insertpar(19) = DatabaseHelper.NewParameter(":UOM2ID", DBNull.Value, OracleType.VarChar)
        End If
        If Not entity.SurplusEmployeeID Is Nothing Then
            insertpar(20) = DatabaseHelper.NewParameter(":SurplusEmployeeID", entity.SurplusEmployeeID, OracleType.VarChar)
        Else
            insertpar(20) = DatabaseHelper.NewParameter(":SurplusEmployeeID", DBNull.Value, OracleType.VarChar)
        End If
        If entity.StepInfoID IsNot Nothing Then
            insertpar(21) = DatabaseHelper.NewParameter(":STEPINFOID", entity.StepInfoID, OracleType.VarChar)
        Else
            insertpar(21) = DatabaseHelper.NewParameter(":STEPINFOID", DBNull.Value, OracleType.VarChar)
        End If
        If entity.Specimenqty <> 0 Then
            insertpar(22) = DatabaseHelper.NewParameter(":Specimenqty", entity.Specimenqty, OracleType.Number)
        Else
            insertpar(22) = DatabaseHelper.NewParameter(":Specimenqty", DBNull.Value, OracleType.Number)
        End If
        If entity.Specimenlossqty <> 0 Then
            insertpar(23) = DatabaseHelper.NewParameter(":Specimenlossqty", entity.Specimenlossqty, OracleType.Number)
        Else
            insertpar(23) = DatabaseHelper.NewParameter(":Specimenlossqty", DBNull.Value, OracleType.Number)
        End If

        insertpar(24) = DatabaseHelper.NewParameter("reasontype", IIf(IsNothing(entity.ReasonType), String.Empty, entity.ReasonType), OracleType.VarChar)
        insertpar(25) = DatabaseHelper.NewParameter("reasondesc", IIf(IsNothing(entity.ReasonDesc), String.Empty, entity.ReasonDesc), OracleType.VarChar)
        If entity.FirstContainerNo IsNot Nothing Then
            insertpar(26) = DatabaseHelper.NewParameter(":firstcheckcontainerno", entity.FirstContainerNo, OracleType.VarChar)
        Else
            insertpar(26) = DatabaseHelper.NewParameter(":firstcheckcontainerno", DBNull.Value, OracleType.VarChar)
        End If
        'add zhunan 2022.6.15 报工增加录入关键过程批次号
        If entity.KeyProcessBatchNo IsNot Nothing Then
            insertpar(27) = DatabaseHelper.NewParameter(":keyprocessbatchno", entity.KeyProcessBatchNo, OracleType.VarChar)
        Else
            insertpar(27) = DatabaseHelper.NewParameter(":keyprocessbatchno", DBNull.Value, OracleType.VarChar)
        End If
        'add by zhangrj 2022-07-18 增加备注
        If entity.Note IsNot Nothing Then
            insertpar(28) = DatabaseHelper.NewParameter(":notes", entity.Note, OracleType.VarChar)
        Else
            insertpar(28) = DatabaseHelper.NewParameter(":notes", DBNull.Value, OracleType.VarChar)
        End If
        Return New Object() {strsql, insertpar}

    End Function
#End Region

End Class
